/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package modele;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nishruu
 */
public class BD {

    private static final String DRIVER_NAME = "org.postgresql.Driver";

    static final String nombase = "jdbc:postgresql://localhost/miage";
    static final String user = "postgres";
    static final String pwd = "postgres";

    /**
     *
     * @return
     */
    public static Connection connect() {
        try {
            Class.forName(DRIVER_NAME);
            return DriverManager.getConnection(nombase,user,pwd);
        }
        catch (ClassNotFoundException ex) {
            Logger.getLogger(BD.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch(SQLException e) {
            System.out.println("Connexion non effectuée : " + e);
        }
        return null;
    }
    
    /**
     *
     * @param user
     * @param pwd
     * @return
     */
    public static User authentification(String user, String pwd) {

        String query1 = "SELECT COUNT(*) FROM CANDIDAT C WHERE C.id_ldap='" + user + "' AND C.password='" + pwd +"'";
        String query11 = "SELECT E.id_etudiant FROM CANDIDAT C, ETUDIANT_SESSION E WHERE C.id_ldap='" + user + "' AND C.password='" + pwd +"' AND C.id_candidat = E.id_candidat";
        String query2 = "SELECT COUNT(*) FROM FORMATEUR WHERE id_ldap='" + user + "' AND password='" + pwd +"'";
        String query21 = "SELECT id_formateur FROM FORMATEUR WHERE id_ldap='" + user + "' AND password='" + pwd +"'";
        String query3 = "SELECT COUNT(*) FROM ADMINISTRATEUR WHERE identifiant='" + user + "' AND password='" + pwd +"'";
        ResultSet results;
        Connection c = connect();

        try {
            Statement stmt = c.createStatement();
            results = stmt.executeQuery(query1);
            results.next();
            int interResult =  results.getInt(1);
            if (interResult > 0) {
                results = stmt.executeQuery(query11);
                results.next();
                return Etudiant.getById(results.getInt(1));
            }
            else{
                results = stmt.executeQuery(query2);
                results.next();
                interResult =  results.getInt(1);
                if (interResult > 0){
                    results = stmt.executeQuery(query21);
                    results.next();
                    return Formateur.getById(results.getInt(1));
                }
                else{
                    results = stmt.executeQuery(query3);
                    results.next();
                    interResult =  results.getInt(1);
                    if (interResult > 0) {
                        return new User(1, "admin", "admin");
                    }
                    else
                        return null;
                }
            }
        }
        catch(Exception e){
            System.out.println("pas de resultats");
            return null;
        }
    }
  /** Ferme une connexion, si elle est non null.
   * Si une exception SQLException est levée, ne la propage pas.
   * @param cx 
   * @throws SQLException
   */
  public static void close(final Connection cx) throws SQLException {
    if (cx != null) {
      try {
        cx.close();
      }
      catch (SQLException exc) {
        System.err.println("Impossible to close connection");
        System.err.println(exc.getMessage());
      }
    }
  }

  /** Ferme une requête SQL, si elle est non null.
   * Si une exception SQLException est levée, ne la propage pas.
   * @param st 
   * @throws SQLException
   */
  public static void close(final Statement st) throws SQLException {
    if (st != null) {
      try {
        st.close();
      }
      catch (SQLException exc) {
        System.err.println("Impossible to close statement");
        System.err.println(exc.getMessage());
      }
    }
  }

  /** Ferme un ResultSet, s'il est non null.
   * Si une exception SQLException est levée, ne la propage pas.
   * @param rs
   * @throws SQLException
   */
  public static void close(final ResultSet rs) throws SQLException {
    if (rs != null) {
      try {
        rs.close();
      }
      catch (SQLException exc) {
        System.err.println("Impossible to close resultSet");
        System.err.println(exc.getMessage());
      }
    }
  }
    /*public static void projetsdemembre(String user, List<Projet> lpro, List<Groupe> lgro) {

        String query = "SELECT P.*,G.* FROM appartient_mem_gro AMG, PROJET P, GROUPE G, MEMBRE M WHERE AMG.gro_id = G.gro_id AND AMG.mem_id = M.mem_id AND P.pro_id = G.pro_id and M.mem_login = \'" + user + "\'";
        ResultSet results;
        Connection c = connect();

        try {
            Statement stmt = c.createStatement();
            ResultSet res = stmt.executeQuery(query);
            while (res.next() != false) {
                Projet p = new Projet();
                Groupe g = new Groupe();
                p.setPro_id(res.getInt("P.pro_id"));
                p.setPro_libelle(res.getString("pro_libelle"));
                p.setPro_description(res.getString("pro_description"));
                p.setPro_dateDebut(res.getDate("pro_datedebut"));
                p.setPro_dateFin(res.getDate("pro_datefin"));
                p.setPro_nbMinGroupe(res.getInt("pro_nbmingroupe"));
                p.setPro_nbMaxGroupe(res.getInt("pro_nbmingroupe"));

                lpro.add(p);

                g.setGro_id(res.getInt("gro_id"));
                g.setGro_libelle(res.getString("gro_libelle"));
                g.setPro_id(res.getInt("G.pro_id"));
                lgro.add(g);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }*/
}
